//Sqlite 本地数据库 用于缓存本地数据
const dbName = "Safety"; //数据库名称
const dbPath = "_doc/safety.db"; //数据库路径

// 判断数据库是否打开
function IsOpen() {
	// 数据库打开了就返回 true,否则返回 false
	var open = plus.sqlite.isOpenDatabase({
		name: dbName, // 数据库名称
		path: dbPath // 数据库地址
	})
	return open;
}

// 创建数据库 或 有该数据库就打开
function OpenSqlite() {
	return new Promise((resolve, reject) => {
		// 打开数据库
		plus.sqlite.openDatabase({
			name: dbName,
			path: dbPath,
			success(e) {
				resolve(true); // 成功回调
			},
			fail(e) {
				resolve(false); 
				// reject(false); // 失败回调
			}
		})
	})
}

// 关闭数据库
function CloseSqlite() {
	return new Promise((resolve, reject) => {
		plus.sqlite.closeDatabase({
			name: dbName,
			success(e) {
				resolve(true);
			},
			fail(e) {
			    resolve(false); 
				// reject(false);
			}
		})
	})
}



// 数据库建表 sql:'CREATE TABLE IF NOT EXISTS tabName("id" varchar(50),"name" TEXT) 
// 创建 CREATE TABLE IF NOT EXISTS 、 tabName 是表名，不能用数字开头、括号里是表格的表头
/**
 * @param {Object} tabName
 * @param {Object} model  数据模型 必须包含id。例如 {id,name,data}
 */
function CreateTable(tabName, model) {
	return new Promise((resolve, reject) => {
		// executeSql: 执行增删改等操作的SQL语句
		plus.sqlite.executeSql({
			name: dbName,
			sql:`CREATE TABLE IF NOT EXISTS ${tabName}(${CreateTableHeader(model)})`,
			success(e) {
				resolve(e);
			},
			fail(e) {
				reject(e);
			}
		})
	})
}
//创建表头
/**
 * @param {*} model 数据模型
 */
function CreateTableHeader(model){
	let keys =  Object.keys(model);;
	var tableHeader = "";
	for(var key of keys){
		if(key=='id'){
			 tableHeader+=`'${key}' varchar(50)`
		}else{
			 tableHeader+=`,'${key}' TEXT`
		}
		   
    };
	console.log(tableHeader)
	return tableHeader;
}

// 数据库删表 sql:'DROP TABLE tabName'
function DropTable(tabName) {
	return new Promise((resolve, reject) => {
		plus.sqlite.executeSql({
			name: dbName,
			sql: `DROP TABLE ${tabName}`,
			success(e) {
				resolve(e);
			},
			fail(e) {
				reject(e);
			}
		})
	})
}
//删除表格中指定id的数据
function DeleteTableRow(tabName,id){
	return new Promise((resolve,reject)=>{
		plus.sqlite.executeSql({
			name:dbName,
			sql:`DELETE FROM ${tabName} WHERE id=${id}`
		})
	})
}

// 查询所有数据库表名
function QueryDBTable() {
	return new Promise((resolve, reject) => {
		plus.sqlite.selectSql({
			name: dbName,
			sql: "select * FROM sqlite_master where type='table'",
			success(e) {
				console.log(e)
				resolve(e);
			},
			fail(e) {
				console.log(e)
				reject(e);
			}
		})
	})
}

// 查询表是否存在
function QueryIsTable(tabName) {
	return new Promise((resolve,reject)=>{
		plus.sqlite.selectSql({
			name: dbName,
			sql: `select count(*) as isTable FROM sqlite_master where type='table' and name='${tabName}'`,
			success(e) {
				console.log(e)
				resolve(e[0].isTable ? true : false);
			},
			fail(e) {
				console.log(e)
				reject(e);
			}
		})
	})
}


// 向表格里添加数据 sql:'INSERT INTO tabName VALUES('x','x','x')'   对应新增
// 或者 sql:'INSERT INTO tabName ('x','x','x') VALUES('x','x','x')'   具体新增
// 插入 INSERT INTO  、 tabName 是表名、根据表头列名插入列值
function InsertTableData(tabName, data, condition) {
	// 判断有没有传参
	if (tabName !== undefined && data !== undefined) {
		// 判断传的参是否有值
		var bol = (JSON.stringify(data) == "{}");
		if (!bol) {
			if (condition == undefined) {
				var sql = `INSERT INTO ${tabName} VALUES(${data})`;
			} else {
				var sql = `INSERT INTO ${tabName} (${condition}) VALUES(${data})`;
			}
			// console.log(sql);
			return new Promise((resolve, reject) => {
				// 表格添加数据
				plus.sqlite.executeSql({
					name: dbName,
					sql: sql,
					success(e) {
						resolve(e);
					},
					fail(e) {
						reject(e);
					}
				})
			})
		} else {
			return new Promise((resolve, reject) => {
				reject("错误添加")
			})
		}
	} else {
		return new Promise((resolve, reject) => {
			reject("错误添加")
		})
	}
}


// 查询表数据总条数
function QueryCount(tabName) {
	return new Promise((resolve, reject) => {
		plus.sqlite.selectSql({
			name: dbName,
			sql: "select count(*) as num from " + tabName,
			success(e) {
				resolve(e);
			},
			fail(e) {
				reject(e);
			}
		})
	})
}

// 获取数据库分页数据
/**
 * 
 * @param {*} tabName 
 * @param {*} num 页码
 * @param {*} size 页面大小返回条数
 * @param {*} byName 排序主键字段
 * @param {*} byType 排序类型 desc倒序 / asc正序
 */
async function QueryDataList(tabName, num, size, byName, byType) {
	let count = 0
	let sql = ''
	let numindex = 0
	await queryCount(tabName).then((resNum) => {
		count = Math.ceil(resNum[0].num / size)
	})
	if (((num - 1) * size) == 0) {
		numindex = 0
	} else {
		numindex = ((num - 1) * size) + 1
	}
	sql = `select * from ${tabName}`
	if (byName && byType) {
		// desc asc
		sql += ` order by ${byName} ${byType}`
	}
	sql += ` limit ${numindex},${size}`
	if (count < num - 1) {
		return new Promise((resolve, reject) => {
			reject("无数据")
		});
	} else {
		return new Promise((resolve, reject) => {
			plus.sqlite.selectSql({
				name: dbName,
				// sql: "select * from userInfo limit 3 offset 3",
				sql: sql,
				success(e) {
					resolve(e);
				},
				fail(e) {
					reject(e);
				}
			})
		})
	}
}

// 修改（更新）数据
// 示例：UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;
// UPDATE 表名 SET 要修改字段 = '修改内容' WHERE 筛选条件 = 6;
/**
 * 
 * @param {*} tabName 表名
 * @param {*} setData 设置值 （修改字段 + 修改内容）
 * @param {*} setName 筛选条件
 * @param {*} setVal 筛选值
 * @returns 
 */
function UpdateSqlData(tabName, setData, setName, setVal) {
	if (JSON.stringify(setData) !== '{}') {
		let dataKeys = Object.keys(setData)
		let setStr = ''
		dataKeys.forEach((item, index) => {
			// console.log(item, setData[item])
			setStr += (
				`${item} = ${JSON.stringify(setData[item])}${dataKeys.length - 1 !== index ? "," : ""}`)
		})
		console.log(setStr)
		return new Promise((resolve, reject) => {
			plus.sqlite.executeSql({
				name: dbName,
				sql: `update ${tabName} set ${setStr} where ${setName} = "${setVal}"`,
				success(e) {
					//resolve(e);
					resolve(100);
				},
				fail(e) {
					console.log(e)
					reject(e);
				}
			})
		})
	} else {
		return new Promise((resolve, reject) => {
			reject("错误")
		});
	}
}

/*
 ** 查询数据库数据
 * @tabName  表名
 * @setData  查询条件{"id":1,"type":"goods"}
 * @byName   排序的字段名
 * @byType   排序规则  asc升序   desc降序
 */
function SelectDataList(tabName, setData, byName, byType) {
	let setStr = ''
	let sql = ''
	if (setData && JSON.stringify(setData) !== '{}') {
		let dataKeys = Object.keys(setData)
		dataKeys.forEach((item, index) => {
			console.log(setData[item])
			setStr += (
				`${item}=${JSON.stringify(setData[item])}${dataKeys.length - 1 !== index ? " and " : ""}`)
		})
		sql = `select * from ${tabName} where ${setStr}`
	} else {
		sql = `select * from ${tabName}`
	}
	if (byName && byType) {
		// desc asc
		sql += ` order by ${byName} ${byType}`
	}
	console.log(sql)
	if (tabName !== undefined) {
		return new Promise((resolve, reject) => {
			plus.sqlite.selectSql({
				name: dbName,
				sql: sql,
				success(e) {
					resolve(e);
				},
				fail(e) {
					console.log(e)
					reject(e);
				}
			})
		})
	} else {
		return new Promise((resolve, reject) => {
			reject("错误")
		});
	}
}
export default {
	IsOpen,
	OpenSqlite,
	CloseSqlite,
	CreateTable,
	DropTable,
	DeleteTableRow,
	QueryIsTable,
	QueryDBTable,
	InsertTableData,
	UpdateSqlData,
	SelectDataList,
	QueryCount,
	QueryDataList
};